package com.no1ikun.service;

import com.no1ikun.bean.Student;
import com.no1ikun.util.JDBCUtil;
import com.no1ikun.util.NuUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentServiceImpl implements StudentService {

    public static String sno;

    @Override
    public Student login(String number, String password) {
        //去数据库查询是否有这个学生
        String sql = "select * from Student where sno=?";
        //使用手机号码或者学号登入
        ResultSet rs = JDBCUtil.executeQuery(sql, number);
        //创建一个学生对象
        Student stu = null;
        try {
            if (rs.next()) {

                String sno = rs.getString("sno");
                String spwd = rs.getString("spwd");
                String sname = rs.getString("sname");
                String sex = rs.getString("sex");
                String birthday = rs.getString("birthday");
                String native_place = rs.getString("native_place");
                String earth_place = rs.getString("earth_place");
                String cno = rs.getString("cno");
                //实例化学生对象
                stu = new Student(sno,spwd,sname,sex,birthday,native_place,earth_place,cno);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        //判断数据库是否查询到学生
        if (stu != null) {
            //判断密码是否正确
            if (stu.getSpwd().equals(password)) {
                //说明用户是正确的
                return stu;
            }
        }
        return null;
    }

    @Override
    public int insertStudent(String name, String password, String sex, String birthday, String earth_place,int cno) {
        String sql = "INSERT INTO Student(sno,sname,spwd, sex, birthday,native_place,earth_place,cno) VALUES (?,?,?,?,?,?,?,?);";
        System.out.println(cno+"班级");
        int i = JDBCUtil.executeUpdate(sql, generateSno(cno), name,password,sex, birthday,"",earth_place,"C0000"+cno);
        return i;
    }
    public String generateSno(int cno){
        sno=NuUtil.getNumber(cno);
        System.out.println(sno+"静态变量");
        return sno;
    }

    @Override
    public Object[][] getData() {
        //定义sql语句
        String sql = "select sno,spwd,sname,sex,birthday,native_place,earth_place,s.cno,cname,dname from Student s  JOIN  Class c on s.cno=c.cno ";
        return getObjects(sql);
    }

    //封装一个查询数据的方法
    public Object[][] getObjects(String sql) {
        ResultSet rs = JDBCUtil.executeQuery(sql);
//        创建一个集合用类存放获取到的数据
        List<Student> list = new ArrayList<>();
        try {
            while (rs.next()) {
                //获取数据库的数据
                Student stu = new Student();
                stu.setSno(rs.getString("sno"));
                stu.setSpwd(rs.getString("spwd"));
                stu.setSname(rs.getString("sname"));
                stu.setSex(rs.getString("sex"));
                stu.setBirthday(rs.getString("birthday"));
                stu.setNative_place(rs.getString("native_place"));
                stu.setEarth_place(rs.getString("earth_place"));
                stu.setCno(rs.getString("cname"));
                stu.setCno(rs.getString("dname"));
                list.add(stu);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        Object[][] stus = new Object[list.size()][9];
        for (int i = 0; i < stus.length; i++) {
            stus[i][0] = list.get(i).getSno();
            stus[i][1] = list.get(i).getSpwd();
            stus[i][2] = list.get(i).getSname();
            stus[i][3] = list.get(i).getSex();
            stus[i][4] = list.get(i).getBirthday();
            stus[i][5] = list.get(i).getNative_place();
            stus[i][6] = list.get(i).getEarth_place();
            stus[i][7] = list.get(i).getCno();
        }

        return stus;
    }

    @Override
    public Object[][] selectLikeStudent(String text) {
        //更具关键字查询
        String sql = "select sno,spwd,sname,sex,birthday,native_place,earth_place,s.cno,cname,dname from Student s JOIN  Class c on s.cno=c.cno  where sno  like '%" + text + "%' or sname like '%" + text + "%' or sex like '%" + text + "%' or birthday like '%" + text + "%' or native_place like '%" +text+  "%' or dname like '%" + text + "%' or earth_place like '%" + text  + "%' or s.cno like '%" + text + "%'";
        System.out.println(sql);
        return getObjects(sql);
    }

    @Override
    public int deleteStudentBySno(String text1) {
        //先去数据库查询是否有这个学生
        //完成删除操作
        String sql1="delete from RewardPunishments where sno=?";
        JDBCUtil.executeUpdate(sql1, text1);
        String sql2 = "delete from Student where sno=?";
        int i = JDBCUtil.executeUpdate(sql2, text1);
        return i;
    }

    @Override
    public boolean checkedStudent(String text) {
        String sql = "select * from Student where sno=?";
        ResultSet resultSet = JDBCUtil.executeQuery(sql, text);

        try {
            //判断是否查询到用户
            if (resultSet.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public Student selectByNumberOrPhone(String info) {
        String sql = "select *  from Student where sno=?";
        ResultSet rs = JDBCUtil.executeQuery(sql, info);
        try {
            if (rs.next()) {
                //获取数据库的数据
                Student stu = new Student();
                stu.setSno(rs.getString("sno"));
                stu.setSpwd(rs.getString("spwd"));
                stu.setSname(rs.getString("sname"));
                stu.setSex(rs.getString("sex"));
                stu.setBirthday(rs.getString("birthday"));
                stu.setNative_place(rs.getString("native_place"));
                stu.setEarth_place(rs.getString("earth_place"));
                stu.setCno(rs.getString("cno"));
                return stu;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    @Override
    public int updateByNumberOrPhone(Student stu) {
        String sql = "UPDATE Student SET spwd = ?, sname = ?,  sex = ?, birthday = ?, native_place = ?, earth_place = ?, cno = ? WHERE sno=?";        System.out.println(sql);

        int i = JDBCUtil.executeUpdate(sql, stu.getSpwd(), stu.getSname(), stu.getSex(), stu.getBirthday(), stu.getNative_place(), stu.getEarth_place(), stu.getCno(), stu.getSno());
        return i;
    }

    @Override
    public String selectClassMsg() {
//        不需要
        return null;
    }




}
